package com.fingard.dsp.localdb;

import com.fingard.FGBiz;
import com.fingard.constant.Format;
import com.fingard.db.ConnSet;
import com.fingard.db.JDBCHelper;
import com.fingard.dsp.bank.dbobj.receipt.ReceiptDataHelper;
import com.fingard.dsp.bank.dbobj.seq.SeqDataHelper;
import com.fingard.dsp.bank.helpers.AskNumAmtHelper;
import com.fingard.dsp.localdb.dbtable.TableNotice;
import com.fingard.io.FileHelper;
import com.fingard.text.StringHelper;

import java.io.File;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class PartitionTable {

    public PartitionTable() {
        hashConnSet = new HashMap<String, ConnSet>();
    }

    private HashMap<String, ConnSet> hashConnSet;

    /**
     * 按日期分区获取本地该分区文件数据库的连接，按月分区
     *
     * @param pTenant   租户
     * @param pPartDate 日期
     * @throws Exception
     */
    public Connection getLoaclDbConnByPart(String pTenant, Date pPartDate) throws Exception {

        if (FGBiz.dspCenter.dspSet.disableLocalDB) {
            throw new Exception("本地数据库已被禁用");
        }

        String yearMo = Format.Date6YearMonth.format(pPartDate);
        if (!hashConnSet.containsKey(yearMo)) {
            String dbFileByMonth = FGBiz.dspCenter.dspSet.getLocalDbFileByMonth(pTenant, pPartDate);
            if (StringHelper.isNullOrEmpty(dbFileByMonth)) {
                return null;
            }
            synchronized (this) {
                File yFile = new File(dbFileByMonth);
                if (!yFile.exists()) {
                    yFile.createNewFile();
                }
                ConnSet addSet = new ConnSet();
                addSet.dbDriver = "org.sqlite.JDBC";
                addSet.dbUrl = "jdbc:sqlite:" + dbFileByMonth;
                hashConnSet.put(yearMo, addSet);
                initTable(addSet);
            }
        }
        return hashConnSet.get(yearMo).getConnection();
    }

    /**
     * 按业务类型和日期分区获取本地该分区文件数据库的连接，按月分区
     *
     * @param pTenant   租户
     * @param pPartDate 日期
     * @param pBizType  业务类型枚举
     * @throws Exception
     */
    public Connection getDbConnByBiz(String pTenant, Date pPartDate, BizDbType pBizType) throws Exception {

        if (FGBiz.dspCenter.dspSet.disableLocalDB) {
            throw new Exception("本地数据库已被禁用");
        }
        if (pBizType == BizDbType.PayNotice) {
            String yearMoAndTenant = Format.Date6YearMonth.format(pPartDate);
            String year = yearMoAndTenant.substring(0, 4);
            if (StringHelper.hasAnyChar(pTenant)) {
                yearMoAndTenant = yearMoAndTenant + "." + pTenant;
            }
            yearMoAndTenant += ".payNotice";

            if (!hashConnSet.containsKey(yearMoAndTenant)) {

                String paDir = StringHelper.getFileParentDir(FGBiz.dspCenter.dspSet.localDbFile);
                if (StringHelper.isNullOrEmpty(paDir)) {
                    throw new Exception("未配置localDbFile");
                }
                String dbFilePath = StringHelper.trimEndFileSp(paDir) + File.separator + year;
                FileHelper.mkdirsIfNotExists(dbFilePath);
                dbFilePath += File.separator + yearMoAndTenant + ".db";

                synchronized (this) {
                    File ymFile = new File(dbFilePath);
                    if (!ymFile.exists()) {
                        ymFile.createNewFile();
                    }
                    ConnSet addSet = new ConnSet();
                    addSet.dbDriver = "org.sqlite.JDBC";
                    addSet.dbUrl = "jdbc:sqlite:" + dbFilePath;
                    hashConnSet.put(yearMoAndTenant, addSet);
                    Connection conn = null;
                    try {
                        conn = addSet.getConnection();
                        TableNotice tabNotice = new TableNotice();
                        tabNotice.initTableSinglePayNotice(conn, "CMB01");
                    } catch (Exception ex) {
                        throw (ex);
                    } finally {
                        if (conn != null) {
                            try {
                                conn.close();
                            } catch (Exception ex) {
                                FGBiz.limitMsg.exception("PartitionTable.initTable", ex);
                            }
                        }
                    }
                }
            }
            return hashConnSet.get(yearMoAndTenant).getConnection();
        }
        return null;
    }

    public void initTable(ConnSet pConnSet) throws Exception {
        Connection conn = null;
        try {
            conn = pConnSet.getConnection();
            initTableAskNumberAmt(conn);//流水号表
            initTableReceiptData(conn);//回单数据表
            initTableSeqData(conn);//流水号映射
            initTableDownFile(conn);//文件下载记录表
            initTableSecretkey(conn);//对称密钥保存表
            initTableSeqBatchInfo(conn, "PSBC11");//初始化表
            initTableSeqBatchInfo(conn, "GZDSF01");//初始化表
            initTableSeqSingleInfo(conn, "EPAY01");//初始化表
            initTableSeqBatchInfo(conn, "SPDB12");//初始化表
            initTableSeqRefundInfo(conn, "CMB01");//初始化表
        } catch (Exception ex) {
            throw (ex);
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                } catch (Exception ex) {
                    FGBiz.limitMsg.exception("PartitionTable.initTable", ex);
                }
            }
        }
    }

    /**
     * 记录请求的AskNumber，用于当银行不支持记录DSP发的流水号时，根据结果的金额户名账号匹配取回AskNumber；
     * 表字段含流水号、金额、户名、账号、银行的状态
     *
     * @throws SQLException
     */
    public void initTableAskNumberAmt(Connection pConn) throws SQLException {
        int maxCount = AskNumAmtHelper.getMaxTabCount();
        for (int i = 0; i < maxCount; i++) {
            String tabNameAmt = "ASKNUMAMT" + i;
            if (!isExistTable(pConn, tabNameAmt)) {
                String sqlcreate = "CREATE TABLE " + tabNameAmt + "(" +
                        "REQBATCHNO VARCHAR(64) NOT NULL," +//请求批次号
                        "ORDERNO INTEGER NOT NULL," +//排序字段
                        "ASKNUMBER VARCHAR(64) NOT NULL," +
                        "BANKCODE VARCHAR(10) NOT NULL," +//渠道
                        "PAYSENTTIME DATE," +//报文中的支付发送日期+时间

                        "OPPACT VARCHAR(128)," +
                        "OPPACTNAME VARCHAR(256)," +
                        "AMOUNT DECIMAL(18,2)," +

                        "RESULT VARCHAR(64)," +//状态
                        "MEMO VARCHAR(256)," +//Memo
                        "PAYINFOCODE VARCHAR(128)," +//PayInfoCode
                        "ISBANKRETURNED INTEGER NOT NULL," +//1未更新银行结果，2已更新银行结果
                        "CREATEDON DATE NOT NULL," +
                        "LASTMODIFIEDON DATE NOT NULL," +
                        "ROWVERSION INTEGER NOT NULL," +
                        "UNIQUE(REQBATCHNO,ORDERNO));\r\n" +
                        "CREATE INDEX IX" + tabNameAmt + " ON " + tabNameAmt + " (REQBATCHNO);\r\n";
                FGBiz.logTrace.info("PartitionTable", sqlcreate);
                JDBCHelper.update(pConn, sqlcreate, null);
            }
        }
    }

    /**
     * SeqBat表，批次信息，用于批量代收付，
     * 需记录发银行的批次号， 或者记录银行响应的批次号，
     * 以及文件名，文件上传状态等，
     * 按月份+渠道分表，一个渠道一个表，因为需按顺序取批次序号
     * <p>
     * 使用说明：批量代收付，仅记录整批状态，即一批一条记录
     *
     * @throws SQLException
     */
    public void initTableSeqBatchInfo(Connection pConn, String pBankCode) throws SQLException {
        String tabName = "SEQBAT" + pBankCode.toUpperCase();
        if (!isExistTable(pConn, tabName)) {
            String sqlcreate = "CREATE TABLE " + tabName + "(" +
                    "REQBATCHNO VARCHAR(64) PRIMARY KEY NOT NULL," +//请求批次号
                    "PAYDATE INTEGER NOT NULL," +//支付日期yyyyMMdd
                    "BANKFRONTCODE VARCHAR(32) NOT NULL," +//前置机代码
                    "BATSEQNO INTEGER NOT NULL," +//批次序号，一般是从1开始编的序号，如1，2，3等
                    "PAYSENTTIME DATE NOT NULL," +//报文中的支付发送日期+时间
                    "BANKCODE VARCHAR(16) NOT NULL," +//渠道
                    "TRANSTYPE VARCHAR(16) NOT NULL," +//交易类型，如1988、9188
                    "TOTALNUM INTEGER NULL," +
                    "TOTALAMT DECIMAL(18,2) NULL," +
                    "BANKREQBATCHNO VARCHAR(64)," +//发送给银行的批次号
                    "OUTBATCHNO VARCHAR(64)," +//银行返回的批次号
                    "BUSITYPE VARCHAR(32)," +//业务类型
                    "UPFILENAME VARCHAR(128)," +//上传文件名，仅文件名
                    "UPFILEPATH VARCHAR(256)," +//上传文件保存路径
                    "UPFILESTATE CHAR(1)," +//文件上传状态
                    "NOTIFYSTATE CHAR(1)," +//通知状态
                    "DOWNFILESTATE CHAR(1)," +//文件下载状态
                    "DOWNFILEPATH VARCHAR(256)," +//结果文件下载后保存路径
                    "RESERVE1 VARCHAR(256)," +//保留字段1
                    "RESERVE2 VARCHAR(256)," +//保留字段2
                    "RESERVE3 VARCHAR(256)," +//保留字段3
                    "CREATEDON DATE NOT NULL," +
                    "LASTMODIFIEDON DATE NOT NULL," +
                    "ROWVERSION INTEGER NOT NULL," +
                    "UNIQUE(PAYDATE,BATSEQNO,BANKFRONTCODE));\r\n" +
                    "CREATE INDEX IX1" + tabName + " ON " + tabName + " (REQBATCHNO);\r\n" +
                    "CREATE INDEX IX2" + tabName + " ON " + tabName + " (PAYDATE,BANKFRONTCODE);\r\n" +
                    "CREATE INDEX IX3" + tabName + " ON " + tabName + " (LASTMODIFIEDON);\r\n";
            FGBiz.logTrace.info("PartitionTable", sqlcreate);
            JDBCHelper.update(pConn, sqlcreate, null);
        }
    }

    /**
     * SeqSingle表，单笔信息，用于单笔代收付，
     * 需记录发银行的流水号， 或者记录银行响应的流水号，
     * 以及文件名，文件上传状态等，
     * 按月份+渠道分表，一个渠道一个表，因为需按顺序取流水序号
     * <p>
     * 使用说明：单笔代收付，记录每笔状态，即一笔一条记录
     *
     * @throws SQLException
     */
    public void initTableSeqSingleInfo(Connection pConn, String pBankCode) throws SQLException {
        String tabName = "SEQSINGLE" + pBankCode.toUpperCase();
        if (!isExistTable(pConn, tabName)) {
            String sqlcreate = "CREATE TABLE " + tabName + "(" +
                    "ASKNUMBER VARCHAR(64) PRIMARY KEY NOT NULL," +//业务流水号
                    "BANKCODE VARCHAR(128) NOT NULL," +//渠道
                    "TRANSTYPE VARCHAR(16) NOT NULL," +//交易类型，如1908、9108
                    "PAYDATE INTEGER NOT NULL," +//支付日期yyyyMMdd
                    "BANKFRONTCODE VARCHAR(32) NOT NULL," +//前置机代码
                    "PAYSENTTIME DATE NOT NULL," +//报文中的支付发送日期+时间
                    "BUSINESSDOCUMENTCODE VARCHAR(128) NOT NULL," +//业务号
                    "CARDHOLDER VARCHAR(128)," +//账户户名
                    "CARDBOOKCODE VARCHAR(128)," +//账户号
                    "COMPANYACCOUNT VARCHAR(128)," +//公司账户
                    "MONEY DECIMAL(18,2)," +//交易金额
                    "THIRDPARTTYPE VARCHAR(128)," +//通道类型
                    "SUBCHANNELCODE VARCHAR(128)," +//子渠道
                    "PAYSTATE VARCHAR(10)," +//支付状态码
                    "PAYINFOCODE VARCHAR(128)," +//支付信息码
                    "PAYINFO VARCHAR(128)," +//支付信息
                    "CREATEDON DATE NOT NULL," +//创建日期
                    "LASTMODIFIEDON DATE NOT NULL," +//最近一次修改日期
                    "ROWVERSION INTEGER NOT NULL,\r\n" +//版本号
                    "UNIQUE(PAYDATE,ASKNUMBER));\r\n" +
                    "CREATE INDEX IX" + tabName + " ON " + tabName + " (ASKNUMBER);\r\n";
            FGBiz.logTrace.info("PartitionTable", sqlcreate);
            JDBCHelper.update(pConn, sqlcreate, null);
        }
    }

    /**
     * SeqRefund表，退款信息，用于历史明细信息查询
     * 需记录发银行的流水号， 或者记录银行响应的流水号，
     * 以及退款原因等，
     * 按月份+渠道分表，一个渠道一个表，因为需按顺序取流水序号
     * <p>
     * 使用说明：退款查询，记录每笔退款原因，即一笔一条记录
     *
     * @throws SQLException
     */
    public void initTableSeqRefundInfo(Connection pConn, String pBankCode) throws SQLException {
        String tabName = "SEQREFUND" + pBankCode.toUpperCase();
        if (!isExistTable(pConn, tabName)) {
            String sqlcreate = "CREATE TABLE " + tabName + "(" +
                    "ASKNUMBER VARCHAR(64) PRIMARY KEY NOT NULL," +//业务流水号
                    "BANKCODE VARCHAR(128) NOT NULL," +//渠道
                    "TRANSTYPE VARCHAR(16) NOT NULL," +//交易类型，如1908、9108
                    "BANKFRONTCODE VARCHAR(32) NOT NULL," +//前置机代码
                    "QRYENDDATE DATE NOT NULL," +//报文中的查询日期
                    "REFUNDTICKETREASON VARCHAR(128)," +//退款原因
                    "CREATEDON DATE NOT NULL," +//创建日期
                    "LASTMODIFIEDON DATE NOT NULL," +//最近一次修改日期
                    "ROWVERSION INTEGER NOT NULL,\r\n" +//版本号
                    "UNIQUE(ASKNUMBER,QRYENDDATE));\r\n" +
                    "CREATE INDEX IX" + tabName + " ON " + tabName + " (ASKNUMBER);\r\n";
            FGBiz.logTrace.info("PartitionTable", sqlcreate);
            JDBCHelper.update(pConn, sqlcreate, null);
        }
    }

    public void initTableSeqData(Connection pConn) throws SQLException {
        int maxCount = SeqDataHelper.getMaxTabCount();
        for (int i = 0; i < maxCount; i++) {
            String tabName = "SEQDATA" + i;
            if (!isExistTable(pConn, tabName)) {
                String sqlcreate = "CREATE TABLE " + tabName + "(\r\n" +
                        "OWNSEQ VARCHAR(64) NOT NULL,\r\n" +//本方流水号
                        "OWNACT VARCHAR(64) NOT NULL,\r\n" +//本方账号
                        "OPPSEQ VARCHAR(64) NOT NULL,\r\n" +//对方流水号
                        "SRCSYS VARCHAR(16) NOT NULL,\r\n" +//文件来源系统，对应直联渠道代码，如中行BOC01
                        "CREATEDON DATE NOT NULL,\r\n" +//创建时间
                        "UNIQUE(OWNSEQ,OWNACT,SRCSYS));\r\n" +
                        "CREATE INDEX IXSEQDATA" + i + "OWNSEQ ON " + tabName + " (OWNSEQ,OWNACT);\r\n" +
                        "CREATE INDEX IXSEQDATA" + i + "OPPSEQ ON " + tabName + " (OPPSEQ,OWNACT);";
                FGBiz.logTrace.info("PartitionTable", sqlcreate);
                JDBCHelper.update(pConn, sqlcreate, null);
            }
        }
    }

    /**
     * 电子回单数据表，10个表
     *
     * @throws SQLException
     */
    public void initTableReceiptData(Connection pConn) throws SQLException {
        int maxCount = ReceiptDataHelper.getMaxTabCount();
        for (int i = 0; i < maxCount; i++) {
            String tabName = "RECEIPTDATA" + i;
            if (!isExistTable(pConn, tabName)) {
                String sqlcreate = "CREATE TABLE " + tabName + "(\r\n" +
                        "URID BIGINT PRIMARY KEY NOT NULL,\r\n" +//URID主键
                        "ISTNBR VARCHAR(64) NOT NULL,\r\n" +//回单唯一标识（在银行端的唯一标识，如招行的打印实例号）
                        "RECEIPTNO VARCHAR(64) NOT NULL,\r\n" +//回单关联号（回单编号）
                        "BUSREF VARCHAR(64),\r\n" +//业务参考号
                        "BNKSEQNO VARCHAR(64),\r\n" +//银行流水号
                        "OWNACT VARCHAR(64),\r\n" +//本方账号
                        "BUSREF2 VARCHAR(64),\r\n" +//业务参考号2
                        "BUSREF3 VARCHAR(64),\r\n" +//业务参考号3
                        "BANK VARCHAR(8),\r\n" +//银行代码，如工行102
                        "SRCSYS VARCHAR(16) NOT NULL,\r\n" +//文件来源系统，对应直联渠道代码，如中行BOC01
                        "BUSDATE INT NOT NULL,\r\n" +//日期
                        "OPPACTNUM VARCHAR(64),\r\n" +//--对方账号
                        "OPPACTNAME VARCHAR(128),\r\n" +//--对方户名
                        "MONEYWAY CHAR(1),\r\n" +//--交易方向
                        "CUR VARCHAR(8),\r\n" +//--币种
                        "AMOUNT DECIMAL(18,2),\r\n" +//金额
                        "UPFILE VARCHAR(128),\r\n" +//--上传文件路径或标识（如上传到指定OSS的标识，或者上传至ftp的路径）
                        "LOCALFILE VARCHAR(128),\r\n" +//--回单文件--本地文件相对路径
                        "SRCFILE VARCHAR(128),\r\n" +//源文件路径
                        "FILETYPE VARCHAR(16),\r\n" +//--回单文件类型
                        "DOWNSTATE CHAR(1),\r\n" +//--下载状态：1未下载；2已下载；3下载失败；4下载中
                        "DOWNTIME DATE ,\r\n" +//--下载时间
                        "DOWNINFO VARCHAR(128),\r\n" +//--下载信息，如果失败则为失败原因
                        "UPSTATE CHAR(1),\r\n" +//--上传状态：1未上传；2已上传；3上传失败；4上传中
                        "UPTIME DATE ,\r\n" +//--上传时间
                        "UPINFO VARCHAR(128),\r\n" +//--上传信息，如果失败则为失败原因
                        "PUSHSTATE CHAR(1),\r\n" +//--推送状态：1未推送；2已推送；3推送失败；4推送中
                        "PUSHTIME DATE ,\r\n" +//--推送时间
                        "PUSHINFO VARCHAR(128),\r\n" +//--推送信息，如果失败则为失败原因
                        "TRANCODE VARCHAR(32),\r\n" +//交易代码
                        "RECEIPTTYPE VARCHAR(32),\r\n" +//--回单类型
                        "PURPOSE VARCHAR(128),\r\n" +//--用途
                        "SUMMARY VARCHAR(128),\r\n" +//--摘要（含备注等）
                        "VOUCHERNO VARCHAR(64),\r\n" +//--凭证号码
                        "CHKCODE VARCHAR(64),\r\n" +//--回单验证码
                        "CREATEDON DATE NOT NULL,\r\n" +//创建时间
                        "LASTMODIFIEDON DATE NOT NULL,\r\n" +//最后修改时间
                        "ROWVERSION INTEGER NOT NULL,\r\n" +//-版本号
                        "UNIQUE(ISTNBR,BUSDATE,OWNACT));\r\n" +
                        "CREATE INDEX IXRCPTDATA" + i + "NBR ON " + tabName + " (ISTNBR,OWNACT);\r\n" +
                        "CREATE INDEX IXRCPTDATA" + i + "RCNO ON " + tabName + " (RECEIPTNO,OWNACT);\r\n" +
                        "CREATE INDEX IXRCPTDATA" + i + "REF ON " + tabName + " (BUSREF,OWNACT);\r\n" +
                        "CREATE INDEX IXRCPTDATA" + i + "BNKSEQ ON " + tabName + " (BNKSEQNO,OWNACT);\r\n" +
                        "CREATE INDEX IXRCPTDATA" + i + "BSDATE ON " + tabName + " (BUSDATE,OWNACT);\r\n" +
                        "CREATE INDEX IXRCPTDATA" + i + "REF2 ON " + tabName + " (BUSREF2,OWNACT);\r\n" +
                        "CREATE INDEX IXRCPTDATA" + i + "REF3 ON " + tabName + " (BUSREF3,OWNACT);";
                FGBiz.logTrace.info("PartitionTable", sqlcreate);
                JDBCHelper.update(pConn, sqlcreate, null);
            } else {
                if (!isExistColumn(pConn, tabName, "SRCSYS")) {
                    String sqlAlt = "alter table " + tabName + " add column SRCSYS VARCHAR(16);";
                    FGBiz.logTrace.info("PartitionTable", sqlAlt);
                    JDBCHelper.update(pConn, sqlAlt, null);
                }
                if (!isExistColumn(pConn, tabName, "BUSREF2")) {
                    String sqlAlt = "alter table " + tabName + " add column BUSREF2 VARCHAR(64);";
                    FGBiz.logTrace.info("PartitionTable", sqlAlt);
                    JDBCHelper.update(pConn, sqlAlt, null);
                    sqlAlt = "CREATE INDEX IXRCPTDATA" + i + "REF2 ON " + tabName + " (BUSREF2,OWNACT);";
                    FGBiz.logTrace.info("PartitionTable", sqlAlt);
                    JDBCHelper.update(pConn, sqlAlt, null);
                }
                if (!isExistColumn(pConn, tabName, "BUSREF3")) {
                    String sqlAlt = "alter table " + tabName + " add column BUSREF3 VARCHAR(64);";
                    FGBiz.logTrace.info("PartitionTable", sqlAlt);
                    JDBCHelper.update(pConn, sqlAlt, null);
                    sqlAlt = "CREATE INDEX IXRCPTDATA" + i + "REF3 ON " + tabName + " (BUSREF3,OWNACT);";
                    FGBiz.logTrace.info("PartitionTable", sqlAlt);
                    JDBCHelper.update(pConn, sqlAlt, null);
                }
                if (!isExistColumn(pConn, tabName, "CHKCODE")) {
                    String sqlAlt = "alter table " + tabName + " add column CHKCODE VARCHAR(64);";
                    FGBiz.logTrace.info("PartitionTable", sqlAlt);
                    JDBCHelper.update(pConn, sqlAlt, null);
                }
                if (!isExistColumn(pConn, tabName, "BNKSEQNO")) {
                    String sqlAlt = "alter table " + tabName + " add column BNKSEQNO VARCHAR(64);";
                    FGBiz.logTrace.info("PartitionTable", sqlAlt);
                    JDBCHelper.update(pConn, sqlAlt, null);
                    sqlAlt = "CREATE INDEX IXRCPTDATA" + i + "BNKSEQ ON " + tabName + " (BNKSEQNO,OWNACT);";
                    FGBiz.logTrace.info("PartitionTable", sqlAlt);
                    JDBCHelper.update(pConn, sqlAlt, null);
                }
            }
        }
    }

    /**
     * 用于保存对称密钥
     *
     * @param pConn
     * @throws SQLException
     */
    public void initTableSecretkey(Connection pConn) throws SQLException {
        String tabName = "SECRETKEY".toUpperCase();
        if (!isExistTable(pConn, tabName)) {
            String sqlcreate = "CREATE TABLE " + tabName + "(\r\n" +
                    "URID BIGINT PRIMARY KEY NOT NULL,\r\n" +//URID主键
                    "TENANT VARCHAR(64) NOT NULL,\r\n" +//租户
                    "BUSDATE INT NOT NULL,\r\n" +//日期
                    "KEY VARCHAR(512) NOT NULL,\r\n" +//对称密钥
                    "CREATEDON DATE NOT NULL,\r\n" +//创建时间
                    "LASTMODIFIEDON DATE NOT NULL,\r\n" +//最后修改时间
                    "ROWVERSION INTEGER NOT NULL,\r\n" +//-版本号
                    "UNIQUE(TENANT, BUSDATE));";
            FGBiz.logTrace.info("PartitionTable", sqlcreate);
            JDBCHelper.update(pConn, sqlcreate, null);
        } else {
            if (!isExistColumn(pConn, tabName, "BANKFRONTCODE")) {
                String sqlAlt = "alter table " + tabName + " add column BANKFRONTCODE VARCHAR(64);";
                FGBiz.logTrace.info("PartitionTable", sqlAlt);
                JDBCHelper.update(pConn, sqlAlt, null);
            }
        }
    }


    public void initTableDownFile(Connection pConn) throws SQLException {
        String tabName = "DownFile".toUpperCase();
        if (!isExistTable(pConn, tabName)) {
            String sqlcreate = "CREATE TABLE " + tabName + "(\r\n" +
                    "URID BIGINT PRIMARY KEY NOT NULL,\r\n" +//URID主键
                    "OWNACT VARCHAR(64) NOT NULL,\r\n" +//本方账号
                    "BUSDATE INT NOT NULL,\r\n" +//日期
                    "BANK VARCHAR(8) NOT NULL,\r\n" +//银行代码，如中行104
                    "BANKFRONTCODE VARCHAR(64) NOT NULL,\r\n" +//前置机代码
                    "SRCSYS VARCHAR(16) NOT NULL,\r\n" +//文件来源系统，对应直联渠道代码，如中行BOC01
                    "FILEPATH VARCHAR(128) NOT NULL,\r\n" +//文件相对路径
                    "DOWNSTATE CHAR(1) NOT NULL,\r\n" +//--下载状态：1未下载；2已下载；3下载失败；4下载中
                    "DOWNTIME DATE ,\r\n" +//--下载时间
                    "DOWNINFO VARCHAR(128),\r\n" +//--下载信息，如果失败则为失败原因
                    "CREATEDON DATE NOT NULL,\r\n" +//创建时间
                    "LASTMODIFIEDON DATE NOT NULL,\r\n" +//最后修改时间
                    "ROWVERSION INTEGER NOT NULL,\r\n" +//-版本号
                    "UNIQUE(FILEPATH, BUSDATE, OWNACT, SRCSYS));\r\n" +
                    "CREATE INDEX IXDOWNFBSDT ON " + tabName + " (BUSDATE,OWNACT);\r\n" +
                    "CREATE INDEX IXDPWNFPATH ON " + tabName + " (FILEPATH,OWNACT);\r\n";
            FGBiz.logTrace.info("PartitionTable", sqlcreate);
            JDBCHelper.update(pConn, sqlcreate, null);
        } else {
            if (!isExistColumn(pConn, tabName, "BANKFRONTCODE")) {
                String sqlAlt = "alter table " + tabName + " add column BANKFRONTCODE VARCHAR(64);";
                FGBiz.logTrace.info("PartitionTable", sqlAlt);
                JDBCHelper.update(pConn, sqlAlt, null);
            }
        }
    }

    /**
     * 回单日记录表，按日按账号记录回单获取情况
     */
    public void initTableActDayRecord(Connection pConn) throws SQLException {
        String tabName = "ReceiptDayRecord".toUpperCase();
        if (!isExistTable(pConn, tabName)) {
            String sqlcreate = "CREATE TABLE " + tabName + "(\r\n" +
                    "URID BIGINT PRIMARY KEY NOT NULL,\r\n" +//URID主键
                    "OWNACT VARCHAR(64),\r\n" +//本方账号
                    "BUSDATE INT NOT NULL,\r\n" +//日期
                    "BANK VARCHAR(8),\r\n" +//银行代码，如工行102
                    "BANKFILE VARCHAR(128),\r\n" +//银行回单文件压缩包名
                    "DOWNSTATE CHAR(1),\r\n" +//--下载状态：1未下载；2已下载；3下载失败；4下载中
                    "DOWNTIME DATE ,\r\n" +//--下载时间
                    "DOWNINFO VARCHAR(128),\r\n" +//--下载信息，如果失败则为失败原因
                    "CREATEDON DATE NOT NULL,\r\n" +//创建时间
                    "LASTMODIFIEDON DATE NOT NULL,\r\n" +//最后修改时间
                    "ROWVERSION INTEGER NOT NULL,\r\n" +//-版本号
                    "UNIQUE(BUSDATE, OWNACT));\r\n" +
                    "CREATE INDEX IXRBSDATE ON " + tabName + " (BUSDATE,OWNACT);\r\n";
            FGBiz.logTrace.info("PartitionTable", sqlcreate);
            JDBCHelper.update(pConn, sqlcreate, null);
        }
    }

    /**
     * 判断表是否存在
     */
    public static boolean isExistTable(Connection pConn, String pTabName) throws SQLException {
        String sqlchk = "select count(*) from sqlite_master where type='table' and name=?";
        Object objCount = JDBCHelper.execScalar(pConn, sqlchk, new Object[]{pTabName.toUpperCase()});
        int tCount = Integer.parseInt(objCount.toString());
        if (tCount > 0) {
            return true;
        } else {
            return false;
        }
    }

    public static boolean isExistColumn(Connection pConn, String pTabName, String pChkColumn) throws SQLException {
        String sqlchk = "PRAGMA table_info(" + pTabName + ")";
        List<Map<String, Object>> mapList = JDBCHelper.execQuery(pConn, sqlchk, null);
        if (mapList != null && mapList.size() > 0) {
            for (int i = 0; i < mapList.size(); i++) {
                Map<String, Object> eachMap = mapList.get(i);
                String chkColumn = eachMap.get("NAME").toString();
                if (chkColumn.equalsIgnoreCase(pChkColumn)) {
                    return true;
                }
            }
        }
        return false;
    }
}
